package qlttn.dao.daointerface;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import qlttn.common.utility.HibernateUtility;
import qlttn.common.valueobjects.QuestionVO;
import qlttn.model.Choice;
import qlttn.model.ExamQuestion;
import qlttn.model.Images;
import qlttn.model.MultiChoiceQuestion;
import qlttn.model.TrueFalseQuestion;

public class EditorExamQuestionDAOImpl implements EditorExamQuestionDAO{
	  private Session getSession()
	  {
	  Session s =null;
	     try{
	    	 s=HibernateUtility.getSessionFactory().openSession();
	 		}catch(HibernateException e){
					System.out.println(e.getMessage());
	}
		   return s;
	  }

	@Override
	public List<QuestionVO> loadExamQuestion(int examQID) {
		List<QuestionVO> listQuestion = new ArrayList<QuestionVO>();
		
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int questionID, groupID, creatorID;
		String contentQuestion, kindOfQuestion;
		float score;
		try {
			tx = session.beginTransaction();
			// do some work
			String sql = "select q.questionID, q.groupID, q.creatorID, q.contentQuetion, kindOfQuestion, score from ContentExam c, ExamQuestion e, Question q "+ 
					"where c.examQID = e.examQID and c.examQID = 1 and c.questionID = q.questionID";	

//			String sql = "select * from Question";
			try {
				pres = session.connection().prepareStatement(sql);
//				pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				while(rs.next()){
					//get data from rs
					questionID = rs.getInt(1);
					groupID = rs.getInt(2);
					creatorID = rs.getInt(3); 
					contentQuestion = rs.getString(4);
					kindOfQuestion = rs.getString(5);
					System.out.println("questionID:"+questionID+" groupID:"+groupID+ " creatorID"+creatorID +" contentQuestion:"+contentQuestion+" kindOfQuestion:"+kindOfQuestion);
					score = rs.getFloat(6);
					//set to question
					QuestionVO question = new QuestionVO();
					question.setQuestionID(questionID);
					question.setGroupID(groupID);
					question.setCreatorID(creatorID);
					question.setContentQuestion(contentQuestion);
					question.setKindOfQuestion(kindOfQuestion);
					question.setScore(score);
					listQuestion.add(question);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}
		return listQuestion;
	}
	

	@Override
	public List<QuestionVO> loadQuestion(int courseId, int groupId, String kindOf, String level) {
		List<QuestionVO> listQuestion = new ArrayList<QuestionVO>();
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int questionID, groupID, creatorID;
		String contentQuestion, kindOfQuestion;
		float score;
		try {
			tx = session.beginTransaction();
			// do some work
			
			String sql = "select q.questionID, q.groupID, q.creatorID, q.contentQuetion, kindOfQuestion, score from ContentExam c, GroupQuestion g, Course , Question q "+ 
					"where c.questionID = q.questionID and q.groupID = "+groupId+" and q.groupID = g.groupID and Course.courseId = "+courseId+" and level like '%"+level+"%' and q.KindOfQuestion like '%"+kindOf+"%'" ;	
			if(courseId == 0)
				sql = "select q.questionID, q.groupID, q.creatorID, q.contentQuetion, kindOfQuestion, score from ContentExam c, GroupQuestion g, Course , Question q "+ 
						"where c.questionID = q.questionID and q.groupID = "+groupId+" and q.groupID = g.groupID and Course.courseId = g.courseId  and level like '%"+level+"%' and q.KindOfQuestion like '%"+kindOf+"%'" ;
			if(groupId == 0)
				sql = "select q.questionID, q.groupID, q.creatorID, q.contentQuetion, kindOfQuestion, score from ContentExam c, GroupQuestion g, Course , Question q "+ 
						"where c.questionID = q.questionID and Course.courseId = "+courseId+" and q.groupID = g.groupID and Course.courseId = g.courseId and level like '%"+level+"%' and q.KindOfQuestion like '%"+kindOf+"%'" ;
			
			try {
				pres = session.connection().prepareStatement(sql);
//				pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				int i = 0;
				while(rs.next()){
					//get data from rs
					questionID = rs.getInt(1);
					groupID = rs.getInt(2);
					creatorID = rs.getInt(3); 
					contentQuestion = rs.getString(4);
					kindOfQuestion = rs.getString(5);
					System.out.println("questionID:"+questionID+" groupID:"+groupID+ " creatorID"+creatorID +" contentQuestion:"+contentQuestion+" kindOfQuestion:"+kindOfQuestion);
					score = rs.getFloat(6);
					QuestionVO question = new QuestionVO();
					//set to question
					question.setQuestionID(questionID);
					question.setGroupID(groupID);
					question.setCreatorID(creatorID);
					question.setContentQuestion(contentQuestion);
					question.setKindOfQuestion(kindOfQuestion);
					question.setScore(score);
					question.setDescrible(question.getContentQuestion().substring(0, 20)+"....");
					question.setOrder(++i);
					listQuestion.add(question);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}
		return listQuestion;
	}
	
	//Load choices
	@Override
	public List<Choice> loadListChoice(int idMutil) {
		// TODO Auto-generated method stub
		List<Choice> listChoice = new ArrayList<Choice>();
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int choiceID, mutilID;
		String contentChoice;
		boolean isTrue1;
		Choice choice;
		try {
			tx = session.beginTransaction();
			// do some work
			String sql = "select * from Choice c where c.multiID =" + idMutil;

			// String sql = "select * from Question";
			try {
				pres = session.connection().prepareStatement(sql);
				// pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				while (rs.next()) {
					// get data from rs
					choiceID = rs.getInt(1);
					mutilID = rs.getInt(2);
					contentChoice = rs.getString(3);
					isTrue1 = rs.getBoolean(4);
					System.out.println("choiceID:" + choiceID + " mutilID:"
							+ mutilID + " contentChoice" + contentChoice
							+ " isTrue1:" + isTrue1);
					// set to question
					choice = new Choice();
					choice.setChoiceId(choiceID);
					choice.setContent(contentChoice);
					choice.setIsTrue(isTrue1);
					listChoice.add(choice);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}

		return listChoice;
	}
	
	@Override
	public Images loadImage(int idQuestion) {
		// TODO Auto-generated method stub
		Images images = new Images();
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int imqID,questionID;
		String name="", url;
		try {
			tx = session.beginTransaction();
			// do some work
			String sql = "select * from Images m where m.questionID =" + idQuestion;

			// String sql = "select * from Question";
			try {
				pres = session.connection().prepareStatement(sql);
				// pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				while (rs.next()) {
					// get data from rs
					imqID = rs.getInt(1);
					questionID = rs.getInt(2);
					name = rs.getString(3);
					url = rs.getString(4);
//					System.out.println("mutilID:" + mutilID + " questionID1:"
//							+ questionID1 + " isMutilChoice1" + isMutilChoice1);
					// set to question
					
					images.setImgId(imqID);
					images.setName(name);
					images.setUrl(url);
					
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}
		
		return images;
	}
	
	@Override
	public MultiChoiceQuestion loadMultiQuestion(int questionID) {
		
		MultiChoiceQuestion mutilQuestion = new MultiChoiceQuestion();
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int questionID1, mutilID;
		boolean isMutilChoice1;
		try {
			tx = session.beginTransaction();
			// do some work
			String sql = "select * from MultiChoiceQuestion m where m.questionID =" + questionID;

			// String sql = "select * from Question";
			try {
				pres = session.connection().prepareStatement(sql);
				// pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				while (rs.next()) {
					// get data from rs
					mutilID = rs.getInt(1);
					questionID1 = rs.getInt(2);
					isMutilChoice1 = rs.getBoolean(3);
					System.out.println("mutilID:" + mutilID + " questionID1:"
							+ questionID1 + " isMutilChoice1" + isMutilChoice1);
					// set to question
					
					mutilQuestion.setMultiId(mutilID);
					mutilQuestion.setIsMutilChoice(isMutilChoice1);
					
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}
		return mutilQuestion;
	}
	
	@Override
	public TrueFalseQuestion loadTrueFalseQuestion(int questionID) {
		// TODO Auto-generated method stub

		TrueFalseQuestion trueFalseQuestion = new TrueFalseQuestion();
		Session session = getSession();
		Transaction tx = null;
		PreparedStatement pres;
		int truefalseID,questionID1;
		boolean isTrue1;
		try {
			tx = session.beginTransaction();
			// do some work
			String sql = "select * from TrueFalseQuestion m where m.questionID ="
					+ questionID;

			// String sql = "select * from Question";
			try {
				pres = session.connection().prepareStatement(sql);
				// pres.setInt(1, examQID);
				ResultSet rs = pres.executeQuery();
				while (rs.next()) {
					// get data from rs
					truefalseID = rs.getInt(1);
					questionID1 = rs.getInt(2);
					isTrue1 = rs.getBoolean(3);
					// set to question

					trueFalseQuestion.setResult(isTrue1);

				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			tx.commit();
		} catch (RuntimeException e) {
			if (tx != null) {
				tx.rollback();
			}
			throw e; // or display error message
		} finally {
			session.close();
		}
		return trueFalseQuestion;
	}
	
	//Save Exam Question
		public boolean saveExamQuestion(ExamQuestion examQuestion){
			boolean result = false;
			Session session =getSession();
	 		Transaction tx= session.beginTransaction();
			try {
				tx = session.beginTransaction();
				// do some work
				session.save(examQuestion);
				tx.commit();
				result = true;
			} catch (RuntimeException e) {
				if (tx != null) {
					tx.rollback();
					result = false;
				}
				throw e; // or display error message
			} finally {
				session.close();
			}
			return result;
		}

//	public static void main(String[] args) {
//		EditorExamQuestionDAOImpl examQuestionDAO = new EditorExamQuestionDAOImpl();
//		examQuestionDAO.loadQuestion(1, 1, "", "Easy");
//		
//	}
}
